﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DTO;

namespace DAO
{
    public class daoCuong
    {
        DataProvider db = new DataProvider();
        DataTable dt = new DataTable();
        
        public DataTable dLayDanhSachNVL(int MaDV)
        {
            string sql = "select nvl.TenNL, manl.SoLuongMotPhan from NguyenVatLieu nvl, MonAnNguyenLieu manl where nvl.MaNL=manl.MaNL and MaDV=32 and MaDV  = '" + MaDV + "'";
            return dt = db.ExecuteQuery(sql);
        }
        public DataTable dao_tatca(DateTime dtimebd, DateTime dtkt)
        {
            string sql = "select dv.TenDV , Sum(cthd.SoLuong)as sl , dv.MaDV from CTThucDon cttd, CTHoaDon cthd, DichVu dv where dv.MaDV=cthd.MaDV and dv.MaDV=cttd.MaDV and NgayApDung>='" + dtimebd + "' and NgayApDung<='" + dtkt + "' group by dv.TenDV , cthd.MaDV,dv.MaDV order by sl desc ";
            dt = db.ExecuteQuery(sql);
            return dt;
        }
        //public DataTable dao_NgayBD(DateTime dtimebd)
        //{
        //    string sql = "select dv.TenDV , Sum(cthd.SoLuong)as sl , dv.MaDV from CTThucDon cttd, CTHoaDon cthd, DichVu dv where dv.MaDV=cthd.MaDV and dv.MaDV=cttd.MaDV and NgayApDung>='" + dtimebd + "' group by dv.TenDV , cthd.MaDV,dv.MaDV order by sl desc ";
        //    dt = db.ExecuteQuery(sql);
        //    return dt;
        //}
        //public DataTable dao_ngaykt(DateTime dtimekt, DateTime dtimebd)
        //{
        //    string sql = "select dv.TenDV , Sum(cthd.SoLuong)as sl , dv.MaDV from CTThucDon cttd, CTHoaDon cthd, DichVu dv where dv.MaDV=cthd.MaDV and dv.MaDV=cttd.MaDV and NgayApDung>='" + dtimebd + "' and NgayApDung<='" + dtimekt + "' group by dv.TenDV , cthd.MaDV,dv.MaDV order by sl desc ";
        //    dt = db.ExecuteQuery(sql);
        //    return dt;
        //}
        public DataTable dao_danhsachnguyenlieu(string tennguyenlieu)
        {
            string sql = "select nvl.TenNL, nvl.SoLuongTon, nvl.DonVi, nvl.LoaiNL, cttd.SoPhanBan,nvl.MaNL,  sum((cttd.SoPhanBan*manl.SoLuongMotPhan)) as N'Tổng SL Cần' from NguyenVatLieu nvl, CTThucDon cttd, ThucDonDuTru tddt, MonAnNguyenLieu manl where nvl.MaNL=manl.MaNL and manl.MaDV=cttd.MaDV and cttd.MaTuan=tddt.MaTuan and nvl.TenNL='" + tennguyenlieu + "' group by nvl.TenNL, nvl.SoLuongTon, nvl.DonVi, nvl.LoaiNL,  cttd.SoPhanBan, nvl.MaNL";
            dt = db.ExecuteQuery(sql);
            return dt;
        }
       
        public DataTable dao_thu2(string lbthu2, DateTime dtimebd, DateTime dtkt)
        {
            string sql = "select dv.TenDV as N'Tên Món Ăn', Sum(cthd.SoLuong) as N'Số Phần Bán' from CTThucDon cttd, CTHoaDon cthd, DichVu dv where dv.MaDV=cthd.MaDV and dv.MaDV=cttd.MaDV and cttd.Thu='" + lbthu2 + "' and NgayApDung>='"+ dtimebd+"' and NgayApDung<='"+ dtkt +"' group by dv.TenDV , cthd.MaDV";
            dt = db.ExecuteQuery(sql);
            return dt;
        }
        public DataTable dao_thu3(string lbthu3, DateTime dtimebd, DateTime dtkt)
        {
            string sql = "select dv.TenDV as N'Tên Món Ăn', Sum(cthd.SoLuong) as N'Số Phần Bán' from CTThucDon cttd, CTHoaDon cthd, DichVu dv where dv.MaDV=cthd.MaDV and dv.MaDV=cttd.MaDV and cttd.Thu='" + lbthu3 + "' and NgayApDung>='" + dtimebd + "' and NgayApDung<='" + dtkt + "' group by dv.TenDV , cthd.MaDV";
            dt = db.ExecuteQuery(sql);
            return dt;
        }
        public DataTable dao_thu4(string lbthu4, DateTime dtimebd, DateTime dtkt)
        {
            string sql = "select dv.TenDV as N'Tên Món Ăn', Sum(cthd.SoLuong) as N'Số Phần Bán' from CTThucDon cttd, CTHoaDon cthd, DichVu dv where dv.MaDV=cthd.MaDV and dv.MaDV=cttd.MaDV and cttd.Thu='" + lbthu4 + "' and NgayApDung>='" + dtimebd + "' and NgayApDung<='" + dtkt + "' group by dv.TenDV , cthd.MaDV";
            dt = db.ExecuteQuery(sql);
            return dt;
        }
        public DataTable dao_thu5(string lbthu5, DateTime dtimebd, DateTime dtkt)
        {
            string sql = "select dv.TenDV as N'Tên Món Ăn', Sum(cthd.SoLuong) as N'Số Phần Bán' from CTThucDon cttd, CTHoaDon cthd, DichVu dv where dv.MaDV=cthd.MaDV and dv.MaDV=cttd.MaDV and cttd.Thu='" + lbthu5 + "' and NgayApDung>='" + dtimebd + "' and NgayApDung<='" + dtkt + "' group by dv.TenDV , cthd.MaDV";
            dt = db.ExecuteQuery(sql);
            return dt;
        }
        public DataTable dao_thu6(string lbthu6, DateTime dtimebd, DateTime dtkt)
        {
            string sql = "select dv.TenDV as N'Tên Món Ăn', Sum(cthd.SoLuong) as N'Số Phần Bán' from CTThucDon cttd, CTHoaDon cthd, DichVu dv where dv.MaDV=cthd.MaDV and dv.MaDV=cttd.MaDV and cttd.Thu='" + lbthu6 + "' and NgayApDung>='" + dtimebd + "' and NgayApDung<='" + dtkt + "' group by dv.TenDV , cthd.MaDV";
            dt = db.ExecuteQuery(sql);
            return dt;
        }
        public DataTable dao_thu7(string lbthu7, DateTime dtimebd, DateTime dtkt)
        {
            string sql = "select dv.TenDV as N'Tên Món Ăn', Sum(cthd.SoLuong) as N'Số Phần Bán' from CTThucDon cttd, CTHoaDon cthd, DichVu dv where dv.MaDV=cthd.MaDV and dv.MaDV=cttd.MaDV and cttd.Thu='" + lbthu7 + "' and NgayApDung>='" + dtimebd + "' and NgayApDung<='" + dtkt + "' group by dv.TenDV , cthd.MaDV";
            dt = db.ExecuteQuery(sql);
            return dt;
        }
        public DataTable dao_chunhat(string lbcn, DateTime dtimebd, DateTime dtkt)
        {
            string sql = "select dv.TenDV as N'Tên Món Ăn', Sum(cthd.SoLuong) as N'Số Phần Bán' from CTThucDon cttd, CTHoaDon cthd, DichVu dv where dv.MaDV=cthd.MaDV and dv.MaDV=cttd.MaDV and cttd.Thu='" + lbcn + "' and NgayApDung>='" + dtimebd + "' and NgayApDung<='" + dtkt + "' group by dv.TenDV , cthd.MaDV";
            dt = db.ExecuteQuery(sql);
            return dt;
        }
        public DataTable LayDSThucDonDuTru()
        {
            string sql ="select *  from ThucDonDuTru order by MaTuan desc";
            dt = db.ExecuteQuery(sql);
            return dt;
        }
        public bool ThemThucDonDutru(ThucDonDuTru dto)
        {
            string sql = "insert into ThucDonDuTru(TenTuan,NgayBatDau,NgayKetThuc) values ('"+dto.TenTuan+"','"+dto.NgayBatDau+"','"+dto.NgayKetThuc+"')";
            return db.ExecuteNonQuery(sql);
        }
        public void ThemCTThucDon(CTThucDon dto)
        {
            string sql = "insert into CTThucDon(MaDV,MaTuan,Thu,NgayApDung,SoPhanBan) values (" + dto.MaDV + "," + dto.MaTuan +",'"+dto.Thu+ "','" + dto.NgayApDung + "',"+dto.SoPhanBan+")";
            db.ExecuteNonQuery(sql);
        }
     
    }
}
